package com.dingding.order.core.cancel.sql;

/**
 * @ClassName: CancelOrderSql
 * @Description: 订单通用SQL
 * @author chenwei
 * @date 
 */
public class CancelOrderSql {

	/**
	 * 推送SQL(乘客支付、乘客确认到达和取消订单)
	 */
	public static final String PASSENGER_PAY_PUSH = "SELECT u.aliasId,u.loginEquipment,ud.tellphone,udp.nickName,ud.nickName AS 'driverName',v.lisencePlate FROM userlogin u INNER JOIN userdetail ud ON u.uid = ud.loginId INNER JOIN userdetail udp ON udp.loginId = ? LEFT JOIN vehicleinfo v ON u.uid = v.loginId WHERE u.uid = ?";

	/**
	 * 乘客取消出租车订单
	 * @author YHQ
	 * @since V2.5.6
	 */
	public static final String PASSENGER_CANCEL_TAXI_ORDER = "SELECT u.aliasId,u.clientType as loginEquipment,ud.telPhone,udp.nickName,ud.nickName AS 'driverName',v.lisencePlate FROM specialuserlogin u INNER JOIN specialuserinfo ud ON u.suid = ud.userId INNER JOIN userdetail udp ON udp.loginId = ? LEFT JOIN specialuservehicleinfo v ON u.suid = v.userId WHERE u.suid = ?";
	
	/**
	 * 出租车司机取消出租车订单
	 * @author YHQ
	 * @since V2.5.6
	 */
	public static final String DRIVER_CANCEL_TAXI_ORDER = "SELECT u.aliasId,u.loginEquipment,ud.tellphone,udp.nickName,ud.nickName AS 'driverName',v.lisencePlate FROM userlogin u INNER JOIN userdetail ud ON u.uid = ud.loginId INNER JOIN specialuserinfo udp ON udp.userId = ? LEFT JOIN vehicleinfo v ON u.uid = v.loginId WHERE u.uid = ?";
	
	/**
	 * 自动取消(获取带取消订单列表)
	 */
	public static final String GET_USER_LIST_BY_CANCEL = "SELECT u.oid,u.orderStatus,u.orderType,passengerul.uid AS 'passengerId',passengerul.loginEquipment AS 'passengerEquipment',passengerudl.nickName AS 'passengerName',passengerudl.tellphone     AS 'passengerPhone',driverul.uid               AS 'driverId',driverul.loginEquipment    AS 'driverEquipment',driverudl.nickName         AS 'driverName',driverudl.tellphone        AS 'driverPhone',u.totalMoney,passengerul.aliasId        AS 'passengerAliasId',driverul.aliasId           AS 'driverAliasId',u.distance,u.alipayMoney,u.payMoney,u.couponMoney,u.createTime,u.setOutTime,u.startAddress,u.endAddress,u.isLongDistance,u.ucId,v.lisencePlate FROM userordert u LEFT JOIN userlogin passengerul ON passengerul.uid = ? LEFT JOIN userdetail passengerudl ON passengerudl.loginId = passengerul.uid LEFT JOIN userlogin driverul ON driverul.uid = ? LEFT JOIN userdetail driverudl ON driverul.uid = driverudl.loginId LEFT JOIN vehicleinfo v ON v.loginId = driverudl.loginId";

	
	
	/**
	 * 自动取消(获取带取消订单列表)
	 */
	public static final String GET_USER_LIST_BY_AUTO_CANCEL = "SELECT u.oid,u.orderStatus,u.orderType,passengerul.uid AS 'passengerId',passengerul.loginEquipment AS 'passengerEquipment',passengerudl.nickName AS 'passengerName',passengerudl.tellphone     AS 'passengerPhone',driverul.uid               AS 'driverId',driverul.loginEquipment    AS 'driverEquipment',driverudl.nickName         AS 'driverName',driverudl.tellphone        AS 'driverPhone',u.totalMoney,passengerul.aliasId        AS 'passengerAliasId',driverul.aliasId           AS 'driverAliasId',u.distance,u.alipayMoney,u.payMoney,u.couponMoney,u.createTime,u.setOutTime,u.startAddress,u.endAddress,u.isLongDistance,u.ucId,v.lisencePlate FROM userordert u LEFT JOIN userlogin passengerul ON passengerul.uid = CASE WHEN u.orderType = 2 THEN u.placeOrderUserId ELSE u.grabOrderUserId END LEFT JOIN userdetail passengerudl ON passengerudl.loginId = passengerul.uid LEFT JOIN userlogin driverul ON driverul.uid = CASE WHEN u.orderType = 1 THEN u.placeOrderUserId ELSE u.grabOrderUserId END LEFT JOIN userdetail driverudl ON driverul.uid = driverudl.loginId LEFT JOIN vehicleinfo v ON v.loginId = driverudl.loginId WHERE u.oid = ? ";

	
	/**
	 * 获取所属当前订单子订单数量
	 * @author CHEHUANBO
	 */
	public static final String GET_CHILDREN_COUNT = "SELECT COUNT(*) FROM userordert ut WHERE ut.parOrderId = ?";

	/**
	 * 取消订单
	 * @author CHEHUANBO
	 * 修改说明：
	 * 修改人：fengshuonan
	 * 修改原因：新增一个publishType
	 * 修改时间：2016-07-08
	 * 修改版本：V2.3.0
	 */
	public static final String CANCEL_ORDER = "INSERT INTO userorder (oid, placeOrderUserId, payCity, setOutTime, startAddress, startLongitude, startLatitude, endAddress, endLongitude, endLatitude, distance, payMoney, alipayMoney, totalMoney, number, redBag, message, payDistance, marker, grabOrderUserId, grabOrderTime, orderStatus, createTime, payTime, cancelTime, endTime, orderType, cancelType, cancelNumber, cancelDesc, isLongDistance, couponMoney, ucId, payType, isComment, psgRcvIntegral, driverRcvIntegral, cancelUserId, isCarShare, setOutCondition,publishType) SELECT ut.oid, ut.placeOrderUserId, ut.payCity, ut.setOutTime, ut.startAddress, ut.startLongitude, ut.startLatitude, ut.endAddress, ut.endLongitude, ut.endLatitude, ut.distance, ut.payMoney, ut.alipayMoney, ut.totalMoney, ut.number, ut.redBag, ut.message, ut.payDistance, ut.marker, ut.grabOrderUserId, ut.grabOrderTime, 5, ut.createTime, ut.payTime, NOW(), NOW(), ut.orderType, ?, ?, ?, ut.isLongDistance, ut.couponMoney, ut.ucId, ut.payType, ut.isComment, ut.psgRcvIntegral, ut.driverRcvIntegral, ?, ut.isCarShare, ut.setOutCondition,1 FROM userordert ut WHERE ut.oid = ?";

	/**
	 * 删除临时表中的订单
	 * @author CHEHUANBO
	 */
	public static final String DELETE_ORDER = "DELETE ut FROM userordert ut  WHERE ut.oid = ?";

	
	/**
	 * 
	 * 获取自动取消订单列表
	 * 
	 * @author YHQ
	 * 
	 */
	public static final String AUTO_CANCEL_ORDER_LIST = "SELECT oid, placeOrderUserId,payCity,setOutTime,startAddress,startLongitude,startLatitude,endAddress,endLongitude,endLatitude,distance,payMoney,alipayMoney,payType,ucId,couponMoney,totalMoney, number,redBag,message,payDistance,marker,grabOrderUserId,grabOrderTime,orderStatus,createTime,payTime,endTime,cancelTime,orderType,cancelType,cancelNumber,cancelDesc,isLongDistance,psgRcvIntegral,driverRcvIntegral,cancelUserId,isComment,isCarShare,setOutCondition,freeSeat,parOrderId,isChildren FROM Userordert WHERE (TIMESTAMPDIFF(SECOND,DATE_ADD(grabOrderTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0 AND orderStatus = 2) OR (TIMESTAMPDIFF(SECOND,DATE_SUB(setOutTime,INTERVAL 5 DAY_MINUTE),NOW()) >= 0 AND orderStatus = 1)";
	
	/**
	 * 获取自动取消出租车订单列表
	 * 
	 * @author YHQ
	 */
	public static final String AUTO_CANCEL_TAXI_ORDER_LIST = "SELECT taxiOrdertId,placeOrderUserId,placeOrderCity,setOutTime,startAddress,startLongitude,startLatitude,endAddress,endLongitude,endLatitude,distance,payMoney,alipayMoney,payType,totalMoney,number,message,payDistance,grabOrderUserId,grabOrderTime,orderStatus,createTime,payTime,endTime,cancelTime,orderType,cancelType,cancelDesc,cancelUserId,isComment,transactionType FROM usertaxiordert WHERE orderStatus = 1 AND TIMESTAMPDIFF(SECOND,DATE_ADD(setOutTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0";
	
	/**
	 * 
	 * 获取自动取消出租车订单推送信息
	 * 
	 */
	public static final String GET_TAXI_PUSH_INFO = "SELECT  u.aliasId,u.loginEquipment,u.userName FROM userlogin u WHERE u.uid = ?";
	
	
	/**
	 *获取自动取消专车订单列表 
	 *@author sunzhicheng
	 *@modify by hufan 添加必须字段（,vehicleLevelId）
	 */
	public static final String AUTO_CANCEL_SPECIAL_ORDER_LIST= " SELECT specialOrderId,placeOrderUserId,grabOrderUserId,createTime,placeOrderCityCode,placeOrderCity,setOutTime,setOutType,startAddress,startLongitude,startLatitude,endAddress,endLongitude,endLatitude,duration,distance,payMoney,alipayMoney,totalMoney,estimateDistance,estimateDuration,estimateMoney,payType,payTime,appointTime,aboardTime,endTime,cancelType,cancelDesc,cancelTime,publishType,orderStatus,aboardLongitude,aboardLatitude,debusLongitude,debusLatitude,vehicleLevelId FROM UserSpecialOrderT WHERE orderStatus = 1 AND TIMESTAMPDIFF(SECOND,DATE_ADD(createTime,INTERVAL 10 DAY_MINUTE),NOW()) >= 0 ";
	
	/**
	 * 
	 * 获取自动取消专车车订单推送信息
	 * @author sunzhicheng
	 */
	public static final String GET_SPECIAL_PUSH_INFO = " SELECT  u.aliasId,u.loginEquipment,u.userName telPhone  FROM userlogin u WHERE u.uid = ? ";
	
	
	
	/**
	 * 
	 * @Title: cancelSpecialOrderNoticeInfo 
	 * @Description: 获取取消专车订单通知信息 
	 * @param cancelType 1:乘客  2:司机
	 * @author YHQ 
	 * @return String    返回类型
	 * 
	 */
	public static String cancelSpecialOrderNoticeInfo(int cancelType){
		
		StringBuilder builder = new StringBuilder();
		
		builder.append("SELECT  u.specialOrderId,u.orderStatus,u.publishType,s.aliasId,s.clientType FROM userspecialordert u  INNER JOIN specialuserlogin s ");
		
		if(cancelType == 1){
			builder.append("ON s.suid = u.grabOrderUserId ");
		}else if(cancelType == 2){
			builder.append("ON s.suid = u.placeOrderUserId ");
		}
		
		builder.append("WHERE u.specialOrderId = ?");
		
		return builder.toString();
	}
	
}
